import { Router } from 'express';
import Promise from 'bluebird';
import db from 'sqlite';

const routes = Router();

routes.get('/', (req, res) => {
  res.render('index', { title: 'Express' });
});

routes.get('/archive', async (req, res, next) => {
  let { pageNumber, pageSize, type, order, title } = req.query;
  pageNumber = +pageNumber || 1;
  pageSize = +pageSize || 10;
  order = order || 'asc';

  let where = 'WHERE id <> 0 ';
  if (type) where += `AND type='${type}'`;
  if (title) {
    where += `AND title LIKE '%${title}%'`;
    pageNumber = 1;
  }

  try {
    const [{ total }, rows] = await Promise.all([
      db.get(`SELECT COUNT(*) as total FROM Archive ${where}`),
      db.all(`SELECT * FROM Archive ${where} ORDER BY id ${order} LIMIT ${(pageNumber - 1) * pageSize},${pageSize}`)
    ]);
    res.json({ total, rows });
  } catch (err) {
    res.json({
      total: 0,
      rows: []
    });
    next(err);
  }
});

routes.get('/archive/:id', async (req, res, next) => {
  try {
    const row = await db.get('SELECT * FROM Archive WHERE id=?', req.params.id);
    res.json(row || {});
  } catch (err) {
    res.json({});
    next(err);
  }
});

routes.post('/archive', async (req, res, next) => {
  const form = req.body;
  try {
    const { stmt: { lastID: id } } = await db.run('INSERT INTO Archive (type, title, fullName, year, keepDate, boxNo, unitNo, liablePerson, time, pageCount, secretLevel, remark, fileType, folderName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
    [form.type, form.title, form.fullName, form.year, form.keepDate, form.boxNo, form.unitNo, form.liablePerson, form.time, form.pageCount, form.secretLevel, form.remark, form.fileType, form.folderName]);
    res.json({
      id,
      state: true,
      msg: '添加成功'
    });
  } catch (err) {
    res.json({
      state: false,
      msg: '添加失败'
    });
    next(err);
  }
});

routes.put('/archive/:id', async (req, res, next) => {
  const form = req.body;
  try {
    const { stmt: { changes } } = await db.run('UPDATE Archive SET type=?, title=?, fullName=?, year=?, keepDate=?, boxNo=?, unitNo=?, liablePerson=?, time=?, pageCount=?, secretLevel=?, remark=?, fileType=?, folderName=? WHERE id = ?',
    [form.type, form.title, form.fullName, form.year, form.keepDate, form.boxNo, form.unitNo, form.liablePerson, form.time, form.pageCount, form.secretLevel, form.remark, form.fileType, form.folderName, req.params.id]);
    res.json({
      state: changes > 0,
      msg: `修改${changes > 0 ? '成功': '失败'}`
    });
  } catch (err) {
    res.json({
      state: false,
      msg: '修改失败'
    });
    next(err);
  }
});

routes.delete('/archive/:ids', async (req, res, next) => {
  const isIds = /^\d+(,\d+)*$/.test(req.params.ids);
  if (!isIds) {
    res.json({
      state: false,
      msg: '删除失败, 参数有误'
    });
  };

  try {
    const { stmt: { changes } } = await db.run(`DELETE FROM Archive WHERE id IN (${req.params.ids})`);
    res.json({
      changes,
      state: changes > 0,
      msg: `删除${changes > 0 ? '成功': '失败'}`
    });
  } catch (err) {
    res.json({
      state: false,
      msg: '删除失败'
    });
    next(err);
  }
});

export default routes;
